package DBLayer;

import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBCustomer implements IFDBCustomer {
	private Connection con;

	public DBCustomer() {
		con = DbConnection.getInstance().getDBcon();
	}

	@Override
	public ArrayList<Customer> getAllCustomers(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public Customer findCustomer(int cusID, boolean retrieveAssociation) {
		String wClause = " customerID = '" + cusID + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public Customer searchCustomerName(String attValue,
			boolean retrieveAssociation) {
		String wClause = "name like '%" + attValue + "%'";
		System.out.println("SearchCustomer " + wClause);
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertCustomer(Customer cus) throws Exception {
		// int nextID = GetMax.getMaxId("Select max(customerID) from customer");
		// nextID = nextID + 1;
		// System.out.println("next id = " + nextID);

		int rc = -1;
		String query = "INSERT INTO customer(name, address,city,phone,mail)  VALUES('"
				+ cus.getName()
				+ "','"
				+ cus.getAddress()
				+ "','"
				+ cus.getCity()
				+ "','"
				+ cus.getPhoneNo()
				+ "','"
				+ cus.getMail();

		// nextID + "','" +

		System.out.println("insert : " + query);
		try { // insert new employee + dependent
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex) {
			System.out.println("Customer ikke oprettet");
			throw new Exception("Customer is not inserted correct");
		}
		return (rc);
	}

	@Override
	public int updateCustomer(Customer cus) {
		Customer cusObj = cus;
		int rc = -1;

		String query = "UPDATE customer SET " + "name ='" + cusObj.getName()
				+ "', " + "address ='" + cusObj.getAddress() + "', "
				+ "city ='" + cusObj.getCity() + "', " + "phone ='"
				+ cusObj.getPhoneNo() + "', " + "mail = '" + cusObj.getMail()
				+ "'";
		System.out.println("Update query:" + query);
		try { // update customer
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}// slut try
		catch (Exception ex) {
			System.out.println("Update exception in customer db: " + ex);
		}
		return (rc);
	}

	/*
	 * @Override public Customer searchCustomerID(int cusID, boolean
	 * retrieveAssociation) { String wClause = "name '=" + cusID + "%'";
	 * System.out.println("SearchCustomer " + wClause); return
	 * singleWhere(wClause, retrieveAssociation); }
	 */

	private ArrayList<Customer> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Customer> list = new ArrayList<Customer>();

		String query = buildQuery(wClause);

		try { // read the Customer from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Customer cusObj = new Customer();
				cusObj = buildCustomer(results);
				list.add(cusObj);
			}// end while
			stmt.close();

		}// slut try
		catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	// Singelwhere is used when we only select one Customer
	private Customer singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Customer cusObj = new Customer();

		String query = buildQuery(wClause);
		System.out.println(query);
		try { // read the Customer from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				cusObj = buildCustomer(results);
				// assocaition is to be build
				stmt.close();

			} else { // no Customer was found
				cusObj = null;
			}
		}// end try
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return cusObj;
	}

	// method to build the query
	private String buildQuery(String wClause) {
		String query = "SELECT customerID, name, address,city,phone,mail FROM Customer";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

	// method to build an Customer object
	private Customer buildCustomer(ResultSet results) {
		Customer cusObj = new Customer();
		try { // the columns from the table emplayee are used
			cusObj.setName(results.getString("name"));
			cusObj.setAddress(results.getString("address"));
			cusObj.setCity(results.getString("city"));
			cusObj.setPhoneNo(results.getString("phone"));
			cusObj.setMail(results.getString("mail"));

		} catch (Exception e) {
			System.out.println("error in building the Customer object");
		}
		return cusObj;
	}

}
